package org.yinxue.framework.jdbc;

import org.yinxue.framework.jdbc.annotation.TableName;

import fun.codedesign.yinxue.util.IOUtil;
import fun.codedesign.yinxue.util.ReflectUtil;
import fun.codedesign.yinxue.util.StringUtil;
import fun.codedesign.yinxue.util.map.TwoValueMap;

import javax.sql.DataSource;

import static fun.codedesign.yinxue.util.SQLUtil.*;

import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * 数据库操作类
 *
 * @author zengjian
 * @create 2018-03-14 9:08
 * @see ConfigUtil
 * @since 1.0.0
 */
public class JdbcClient extends Jdbc {

    // private static final Logger LOGGER = LoggerFactory.getLogger(JdbcClient.class);

    private static final Pattern UPPER_CASE_PATTERN = Pattern.compile("[A-Z]");

    private String queryColumsSQL = "select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS WHERE TABLE_NAME = ?";

    private DataSource dataSource;

    public JdbcClient() {
    }

    public JdbcClient(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public <T> Integer insert(final T entity) throws Exception {
        return doExecute(new JdbcAction<Integer>() {
            @Override
            public Integer doAction(Connection con) throws Exception {
                String tableName = doGetTableName(entity);
                // 如果没有该表，那就按照默认规则新建一张表
                if (!checkExsitOnSchema(tableName)) {
                    insertTable(entity, tableName);
                }
                TwoValueMap values = setValuesForInsert(entity, null, doGetColunmNames(tableName));
                int size = values.size();
                PreparedStatement ps = con.prepareStatement(buildInsertSql(tableName, size));
                for (int i = 0; i < size; i++) {
                    ps.setObject(i + 1, values.get(i).getValue2());
                }
                return ps.executeUpdate();
            }
        });
    }

    public <T> Integer insertTable(final T t, final String tableName) throws Exception {
        return doUpate(buildCreateTableSql(t, tableName));
    }

    @Override
    public <T> Integer insertBatch(final List<T> list) throws Exception {
        return doExecute(new JdbcAction<Integer>() {
            @Override
            public Integer doAction(Connection con) throws Exception {
                con.setAutoCommit(false);
                T t = list.get(0);
                String tableName = doGetTableName(t);
                // 如果没有该表，那就按照默认规则新建一张表
                if (!checkExsitOnSchema(tableName)) {
                    insertTable(t, tableName);
                }
                List<Field> fields = ReflectUtil.getFields(t);
                TwoValueMap values = doGetColunmNames(tableName);
                int size = values.size();
                // ps必须在循环外
                PreparedStatement ps = con.prepareStatement(buildInsertSql(tableName, size));
                for (T t1 : list) {
                    values = setValuesForInsert(t1, fields, values);
                    for (int i = 0; i < size; i++) {
                        ps.setObject(i + 1, values.get(i).getValue2());
                    }
                    ps.addBatch();
                }
                int[] counts = ps.executeBatch();
                con.commit();
                ps.clearBatch();
                Integer total = 0;
                for (int count : counts) {
                    total += count;
                }
                return total;
            }
        });
    }

    @Override
    public <T> Integer delete(final T entity) throws Exception {
        BigInteger id = (BigInteger) ReflectUtil.getFieldValue(entity, "id");
        String tableName = doGetTableName(entity);
        return doUpate(toBuildDeleteSql(tableName, id), new Object[]{id});
    }


    @Override
    public <T> Integer update(final T entity) throws Exception {
        String tableName = doGetTableName(entity);
        TwoValueMap values = setValuesForUpdate(entity, doGetColunmNames(tableName));
        return doUpate(toBuildUpdateSql(entity, tableName, values), values.value2ToArray());
    }

    public int[] updateBatch(final String sql, final Object[][] objects) throws Exception {
        return doExecute(new JdbcAction<int[]>() {
            @Override
            public int[] doAction(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                for (int i = 0, size1 = objects.length; i < size1; i++) {
                    for (int j = 0, size2 = objects[i].length; j < size2; j++) {
                        ps.setObject(j + 1, objects[i][j]);
                    }
                    ps.addBatch();
                }
                return ps.executeBatch();
            }
        });
    }

    public <T> T queryObject(final String sql, final Object[] params, final Class<T> returnType) throws Exception {
        return toObject(doQueryListOfMap(sql, params), returnType);
    }



    @Override
    public <T> Integer queryCount(final T entity) throws Exception {
        return doUpate(toBuildSelectCountSql(doGetTableName(entity)));
    }

    @Override
    public <T> List<T> queryList(final T entity) throws Exception {
        List<Map<String,Object>> mapList = doQueryListOfMap(buildSelectSql(doGetTableName(entity)));
        return (List<T>) ReflectUtil.fillFieldValues(mapList, entity.getClass());
    }


    public <T> List<T> queryList(final String sql, final Class<T> returnType) throws Exception {
        List<Map<String,Object>> mapList = doQueryListOfMap(sql);
        return ReflectUtil.fillFieldValues(mapList, returnType);
    }

    List<Map<String, Object>> doQueryListOfMap(final String sql) throws Exception {
        return doQueryListOfMap(sql, new Object[0]);
    }

    List<Map<String, Object>> doQueryListOfMap(final String sql, final Object[] params) throws Exception {
        ResultSet resultSet = doQueryResultSet(sql, params);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<Map<String, Object>> mapList = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, Object> map = new LinkedHashMap<>();
            for (int i = 1; i < columnCount + 1; i++) {
                Object columValue = resultSet.getObject(i);
                String columnName = metaData.getColumnName(i);
                map.put(columnName, columValue);
            }
            mapList.add(map);
        }
        return mapList;
    }

    ResultSet doQueryResultSet(final String sql) throws Exception {
        return doQueryResultSet(sql, new Object[0]);
    }

    ResultSet doQueryResultSet(final String sql, final Object[] params) throws Exception {
        return doExecute(new JdbcAction<ResultSet>() {
            @Override
            public ResultSet doAction(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                for (int i = 0, size = params.length; i < size; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                return ps.executeQuery();
            }
        });
    }

    int doUpate(final String sql) throws Exception {
        return doUpate(sql, new Object[0]);
    }

    int doUpate(final String sql, final Object[] params) throws Exception {
        return doExecute(new JdbcAction<Integer>() {
            @Override
            public Integer doAction(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql);
                for (int i = 0, length = params.length; i < length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                return ps.executeUpdate();
            }
        });
    }

    <T> String doGetTableName(T t) {
        // 查看注解是否存在TableName值，否则采用默认值
        TableName tableName = t.getClass().getAnnotation(TableName.class);
        if (tableName != null && !"".equals(tableName.value())) {
            return tableName.value();
        }
        return doGetDefaultTableName(t);
    }


    /**
     * AbcEntity --> abc_entity
     * @param t
     * @param <T>
     * @return
     */
    <T> String doGetDefaultTableName(T t) {
        String simpleName = t.getClass().getSimpleName();
        String lowerSimpleName = StringUtil.firstChartoLowerCase(simpleName);
        StringBuffer buffer = new StringBuffer(32);
        Matcher matcher = UPPER_CASE_PATTERN.matcher(lowerSimpleName);
        while (matcher.find()) {
            matcher.appendReplacement(buffer, "_" + matcher.group().toLowerCase());
        }
        matcher.appendTail(buffer);
        return buffer.toString();
    }

    TwoValueMap<String, String, Object> doGetColunmNames(final String tableName) throws Exception {
        ResultSet resultSet = doQueryResultSet(queryColumsSQL, new Object[]{tableName});
        TwoValueMap<String, String, Object> twoValueMap = new TwoValueMap<>();
        while (resultSet.next()) {
            String columnName = resultSet.getString(1);
            twoValueMap.put(StringUtil.retainLowerCharAndNum(columnName), columnName, null);
        }
        return twoValueMap;
    }

    private <T> TwoValueMap setValuesForInsert(T t, List<Field> fields, TwoValueMap keyValueTwo) throws IllegalAccessException {
        if (fields == null) {
            fields = ReflectUtil.getFields(t);
        }
        for (Field field : fields) {
            if (field != null) {
                field.setAccessible(true);
                String fieldName = StringUtil.retainLowerCharAndNum(field.getName());
                Object fieldValue = field.get(t);
                if (keyValueTwo.containsKey(fieldName)) {
                    keyValueTwo.put(fieldName, keyValueTwo.getValue1(fieldName), fieldValue);
                }
            }
        }
        return keyValueTwo;
    }

    private <T> TwoValueMap setValuesForUpdate(T t, TwoValueMap keyValueTwo) throws IllegalAccessException {
        List<Field> fields = ReflectUtil.getFields(t);
        // 剔除ID字段
        keyValueTwo.remove("id");
        for (Field field : fields) {
            if (field != null) {
                field.setAccessible(true);
                String fieldName = StringUtil.retainLowerCharAndNum(field.getName());
                Object fieldValue = field.get(t);
                if (keyValueTwo.containsKey(fieldName)) {
                    keyValueTwo.put(fieldName, keyValueTwo.getValue1(fieldName), fieldValue);
                }
            }
        }
        return keyValueTwo;
    }



    private boolean checkExsitOnSchema(String tableName) throws Exception {
        return !(doQueryListOfMap(queryColumsSQL, new String[]{tableName})).isEmpty();
    }



    private <T> T doExecute(JdbcAction<T> action) throws Exception {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            return action.doAction(con);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        } finally {
            IOUtil.close(con);
        }
    }

    private <T> T toObject(List<Map<String, Object>> list, Class<T> clazz) throws Exception {
        if (list == null) {
            return null;
        }
        Map<String, Object> map = list.get(0);
        Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator();
        if (clazz.isPrimitive() || clazz == String.class) {
            return clazz.cast(iterator.next().getValue());
        } else {
            List<Field> fields = ReflectUtil.getFieldsByClass(clazz);
            Object obj = clazz.newInstance();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(obj, map.get(field.getName()));
            }
            return clazz.cast(obj);
        }
    }
}
